package golf.stockinfo.buysellsimulation;

import golf.stockinfo.buysellsimulation.SimulationData.TransactionType;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Map;
import java.util.TreeMap;

import org.joda.time.DateTime;

public class SimulationCommonUtils {
	
	private static DecimalFormat doubleFormat = new DecimalFormat("###,###.00");
	private static DecimalFormat interestFormat = new DecimalFormat("###,###.0000");
	private static DecimalFormat integerFormat = new DecimalFormat("###,###");
	
	private static int CONDITION_ID_SEQ_INCREMENT = 1000;
	
	public static long getNewConditionId(Connection conn) throws SQLException{
		
		long cacheKey = 1262790898720000l;
		Long output = (Long)GlobalCache.getCache(cacheKey);
		
		if(output == null
				|| output % CONDITION_ID_SEQ_INCREMENT == 0){
			PreparedStatement ps = conn.prepareStatement("SELECT CONDITION_ID_SEQ.nextval FROM DUAL");
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				output = rs.getLong(1);
			}else{
				throw new RuntimeException("Error can't select sequence from CONDITION_ID_SEQ");
			}
			
			rs.close();
			ps.close();
			
		}else{
			output++;
		}
		
		GlobalCache.addCache(cacheKey, output);
		return output;
	}
	
	public static ArrayList<MarketData> loadMarketDataDaily(
			Connection conn, 
			Date startDateOfThisWeek,
			Date startDateOfNextWeek,
			String marketName, 
			String currencyPair) throws SQLException{
		
		long cacheKey = 1010000000000000l + ("loadMarketDataDaily" + marketName + currencyPair).hashCode() + startDateOfThisWeek.getTime();
		@SuppressWarnings("unchecked")
		ArrayList<MarketData> output = (ArrayList<MarketData>) GlobalCache.getCache(cacheKey);
		
		if(output == null){
			
			output = new ArrayList<MarketData>();
			
	        PreparedStatement ps = conn.prepareStatement(
	        		"SELECT " +
	        		"  TRADING_DATE" +
	        		" ,OPEN" +
	        		" ,HIGH" +
	        		" ,LOW" +
	        		" ,CLOSE " +
	        		"FROM PRICE_HIST " +
	        		"WHERE MARKET_NAME = ? " +
	        		"  AND CURRENCY_PAIR = ? " +
	        		"  AND TRADING_TYPE = 'DAILY' " +
	        		"  AND TRADING_DATE >= ?" +
	        		"  AND TRADING_DATE < ?" +
	        		"ORDER BY TRADING_DATE");
	        
	        ps.setString(1, marketName);	//EFIN
	        ps.setString(2, currencyPair);	//SET50
	        ps.setDate(3, startDateOfThisWeek);
	        ps.setDate(4, startDateOfNextWeek);
	        
	        ResultSet rsDaily = ps.executeQuery();
	        
	        while (rsDaily.next()) {
	        	
				MarketData marketData = new MarketData();
				marketData.setDate(rsDaily.getDate("TRADING_DATE"));
				marketData.setOpenIndex(rsDaily.getFloat("OPEN"));
				marketData.setHighIndex(rsDaily.getFloat("HIGH"));
				marketData.setLowIndex(rsDaily.getFloat("LOW"));
				marketData.setCloseIndex(rsDaily.getFloat("CLOSE"));
				
				output.add(marketData);
	        }
	        
	        rsDaily.close();
	        ps.close();
	        
	        GlobalCache.addCache(cacheKey, output);
	        
		}
		
		return output;
	}
	
	public static TreeMap<Long, MarketData> loadMarketDataFromDB(
			Connection conn, short smav, String marketName, String currencyPair, String tradingType) throws SQLException{
		
		long cacheKey = 9000000000000000l + ("loadMarketDataFrom" + smav + marketName + currencyPair + tradingType).hashCode();
		
		@SuppressWarnings("unchecked")
		TreeMap<Long, MarketData> output = (TreeMap<Long, MarketData>) GlobalCache.getCache(cacheKey);
		
		if(output != null){
			return output;
		}else{
			output = new TreeMap<Long, MarketData>();
		}
		
        PreparedStatement ps = conn.prepareStatement(
        		"SELECT " +
        		"  TRADING_DATE" +
        		" ,OPEN" +
        		" ,HIGH" +
        		" ,LOW" +
        		" ,CLOSE " +
        		"FROM PRICE_HIST " +
        		"WHERE MARKET_NAME = ? " +
        		"  AND CURRENCY_PAIR = ? " +
        		"  AND TRADING_TYPE = ? " +
        		"ORDER BY TRADING_DATE");
        
        ps.setString(1, marketName);	//EFIN
        ps.setString(2, currencyPair);	//SET
        ps.setString(3, tradingType);	//WEEKLY
        
        ResultSet rsWeekly = ps.executeQuery();
        
        MarketData prevMarketData = null;

		while (rsWeekly.next()) {
			
			MarketData marketData = new MarketData();
			
			DateTime date = new DateTime(rsWeekly.getDate("TRADING_DATE"));
			
			marketData.setDate(rsWeekly.getDate("TRADING_DATE"));
			marketData.setOpenIndex(rsWeekly.getFloat("OPEN"));
			marketData.setHighIndex(rsWeekly.getFloat("HIGH"));
			marketData.setLowIndex(rsWeekly.getFloat("LOW"));
			marketData.setCloseIndex(rsWeekly.getFloat("CLOSE"));
			marketData.setSMAV(smav);
			if(output.size() >= smav - 1){
				float sum = marketData.getCloseIndex();
				int i=0;
				for(Map.Entry<Long, MarketData> c : output.entrySet()){
					i++;
					if( i > (output.size() - smav + 1)){
						sum = sum + c.getValue().getCloseIndex();
					}
				}
				marketData.setCloseIndexSMAV(sum/smav);
			}
			
			if(output.size() > 0){
				prevMarketData.setNextDate(marketData.getDate());
				prevMarketData = marketData;
			}
			
			prevMarketData = marketData;

			output.put(date.getMillis(), marketData);
		}
			
		rsWeekly.close();
		ps.close();
		
		
		ArrayList<Long> tobeDeleted = new ArrayList<Long>();
		for(Map.Entry<Long, MarketData> c : output.entrySet()){
			if(c.getValue().getCloseIndexSMAV() == 0){
				tobeDeleted.add(c.getKey());
			}
		}
		
		for(Long dt:tobeDeleted){
			//This is now really delete the item
			output.remove(dt);
		}
		
		GlobalCache.addCache(cacheKey, output);
		
		return output;
		
	}
	
	public static TreeMap<DateTime, MarketData> loadMarketDataFromDB(
			Connection conn, int smav, String marketName, String currencyPair, String tradingType) throws SQLException{
		
		String cacheKey = "loadMarketDataFrom" + smav + marketName + currencyPair + tradingType;
		
		@SuppressWarnings("unchecked")
		TreeMap<DateTime, MarketData> output = (TreeMap<DateTime, MarketData>) GlobalCache.getCache(cacheKey);
		
		if(output != null){
			return output;
		}else{
			output = new TreeMap<DateTime, MarketData>();
		}
		
        PreparedStatement ps = conn.prepareStatement(
        		"SELECT " +
        		"  TRADING_DATE" +
        		" ,OPEN" +
        		" ,HIGH" +
        		" ,LOW" +
        		" ,CLOSE " +
        		"FROM PRICE_HIST " +
        		"WHERE MARKET_NAME = ? " +
        		"  AND CURRENCY_PAIR = ? " +
        		"  AND TRADING_TYPE = ? " +
        		"ORDER BY TRADING_DATE");
        
        ps.setString(1, marketName);	//EFIN
        ps.setString(2, currencyPair);	//SET
        ps.setString(3, tradingType);	//WEEKLY
        
        ResultSet rsWeekly = ps.executeQuery();
        
        MarketData prevMarketData = null;

		while (rsWeekly.next()) {
			
			MarketData marketData = new MarketData();
			
			DateTime date = new DateTime(rsWeekly.getDate("TRADING_DATE"));
			
			marketData.setDate(rsWeekly.getDate("TRADING_DATE"));
			marketData.setOpenIndex(rsWeekly.getFloat("OPEN"));
			marketData.setHighIndex(rsWeekly.getFloat("HIGH"));
			marketData.setLowIndex(rsWeekly.getFloat("LOW"));
			marketData.setCloseIndex(rsWeekly.getFloat("CLOSE"));
			marketData.setSMAV(smav);
			if(output.size() >= smav - 1){
				float sum = marketData.getCloseIndex();
				Iterator<DateTime> oItr = output.keySet().iterator();
				int i=0;
				while(oItr.hasNext()){
					i++;
					MarketData o = output.get(oItr.next());
					if(i > (output.size() - smav + 1)){
						sum = sum + o.getCloseIndex();
					}
				}
				marketData.setCloseIndexSMAV(sum/smav);
			}
			
			if(output.size() > 0){
				prevMarketData.setNextDate(marketData.getDate());
				prevMarketData = marketData;
			}
			
			prevMarketData = marketData;

			output.put(date, marketData);
		}
			
		rsWeekly.close();
		ps.close();
		
		//To make a mark of item to be clean up for those do not have SMAV for close index
		Iterator<DateTime> oItr = output.keySet().iterator();
		//This has to be defined to avoid ConcurrentModification Exception
		ArrayList<DateTime> tobeDeleted = new ArrayList<DateTime>();
		while(oItr.hasNext()){
			DateTime nextIndx = oItr.next();
			MarketData o = output.get(nextIndx);
			if(o.getCloseIndexSMAV() == 0){
				tobeDeleted.add(nextIndx);
			}
		}
		
		for(DateTime dt:tobeDeleted){
			//This is now really delete the item
			output.remove(dt);
		}
		
		GlobalCache.addCache(cacheKey, output);
		
		return output;
		
	}
	
	public static double roundOff(double toRoundOff, int noOfDigitLeft){
		
		BigDecimal initialValue = new BigDecimal(toRoundOff);
		
		return initialValue.setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue();
		
//		String[] tmpArr = Double.toString(toRoundOff).split("\\.");
//		String fullPart = tmpArr[0];
//		String decimalPart = tmpArr[1];
//		
//		if(decimalPart.length() <= noOfDigitLeft){
//			return toRoundOff;
//		}else if(noOfDigitLeft > 0){
//			return Double.parseDouble(fullPart + "." + decimalPart.substring(0, noOfDigitLeft));
//		}else{
//			return Double.parseDouble(fullPart);
//		}

	}
	
	public static GainLossData getMaxProfitOrLossPerLotDuringWeek(
			Connection conn,
			boolean getLoss, 
			SimulationData simData, 
			MarketData thisWeek, 
			double runningBalance
			) throws SQLException{
		
		GainLossData output = new GainLossData();
		
		long lowestCacheKey =970000000000000l + thisWeek.getDate().getTime();
		long highestCacheKey = 980000000000000l + thisWeek.getDate().getTime();
		long lowestDateCacheKey = 990000000000000l + thisWeek.getDate().getTime();
		long highestDateCacheKey = 1000000000000000l + thisWeek.getDate().getTime();
		
		Double lowestDuringWeek = (Double) GlobalCache.getCache(lowestCacheKey);
		Double highestDuringWeek = (Double) GlobalCache.getCache(highestCacheKey);
		Date lowestDate = (Date) GlobalCache.getCache(lowestDateCacheKey);
		Date highestDate = (Date) GlobalCache.getCache(highestDateCacheKey);
		
		if(lowestDuringWeek == null
				|| highestDuringWeek == null){
			
			PreparedStatement pstmt = conn.prepareStatement(
					"SELECT MIN(LOW), MAX(HIGH)" +
					"  FROM PRICE_HIST " +
					" WHERE TRADING_DATE >= ?" +
					"   AND TRADING_DATE < ?" +
					"   AND CURRENCY_PAIR = 'SET50'" +
					"   AND TRADING_TYPE = 'DAILY'" +
					"   AND MARKET_NAME = 'EFIN'");
			
			pstmt.setDate(1, thisWeek.getDate());
			pstmt.setDate(2, thisWeek.getNextDate());
			
			ResultSet rs = pstmt.executeQuery();
			
			if(rs.next()){
				
				highestDuringWeek = rs.getDouble(2);
				lowestDuringWeek = rs.getDouble(1);
				
			}

			rs.close();
			pstmt.close();
			
			if(highestDuringWeek == 0 || lowestDuringWeek == 0){
				throw new RuntimeException("Something is wrong, highest or lowest during the week is ZERO");
			}
			
			GlobalCache.addCache(highestCacheKey, highestDuringWeek);
			GlobalCache.addCache(lowestCacheKey, lowestDuringWeek);
			
			if(lowestDate == null
					|| highestDate == null){
				PreparedStatement ps = conn.prepareStatement(
						"SELECT T.TRADING_DATE" +
						"  FROM PRICE_HIST T" +
						" WHERE T.TRADING_DATE >=?" +
						"   AND T.TRADING_DATE < ?" +
						"   AND T.LOW = ?" +
						"   AND CURRENCY_PAIR = 'SET50'" +
						"   AND TRADING_TYPE = 'DAILY'" +
						"   AND MARKET_NAME = 'EFIN'" +
						" ORDER BY 1");
				
				ps.setDate(1, thisWeek.getDate());
				ps.setDate(2, thisWeek.getNextDate());
				ps.setDouble(3, lowestDuringWeek);
				
				rs = ps.executeQuery();
				
				if(rs.next()){
					lowestDate = rs.getDate(1);
				}
				
				if(lowestDate == null){
					throw new RuntimeException(
							"Error Can't find lowest date for " + thisWeek.getDate().toString() + " with low point at " + lowestDuringWeek);
				}else{
					GlobalCache.addCache(lowestDateCacheKey, lowestDate);
				}
				
				rs.close();
				ps.close();
				
				ps = conn.prepareStatement(
						"SELECT T.TRADING_DATE" +
						"  FROM PRICE_HIST T" +
						" WHERE T.TRADING_DATE >=?" +
						"   AND T.TRADING_DATE < ?" +
						"   AND T.HIGH = ?" +
						"   AND CURRENCY_PAIR = 'SET50'" +
						"   AND TRADING_TYPE = 'DAILY'" +
						"   AND MARKET_NAME = 'EFIN'" +
						" ORDER BY 1");
				
				ps.setDate(1, thisWeek.getDate());
				ps.setDate(2, thisWeek.getNextDate());
				ps.setDouble(3, highestDuringWeek);
				
				rs = ps.executeQuery();
				
				if(rs.next()){
					highestDate = rs.getDate(1);
				}
				
				if(highestDate == null){
					throw new RuntimeException(
							"Error Can't find highest date for " + thisWeek.getDate().toString() + " with high point at " + highestDuringWeek);
				}else{
					GlobalCache.addCache(highestDateCacheKey, highestDate);
				}
				
				rs.close();
				ps.close();
			}
				
		}
		
		if(getLoss){
			if(simData.getTypeOfTransaction().equals(TransactionType.LONG_TFEX)){
				output.setGainLossAmount((lowestDuringWeek - simData.getStartIndex()) * SimulationConstant.TFEX_PRICE_PER_POINT);
				output.setGainLossDate(lowestDate);
			}else if(simData.getTypeOfTransaction().equals(TransactionType.SHORT_TEFX)){
				output.setGainLossAmount((simData.getStartIndex() - highestDuringWeek) * SimulationConstant.TFEX_PRICE_PER_POINT);
				output.setGainLossDate(highestDate);
			}else if(simData.getTypeOfTransaction().equals(TransactionType.BUY_SET50)){
				output.setGainLossAmount((lowestDuringWeek - simData.getStartIndex())/simData.getStartIndex() * runningBalance);
				output.setGainLossDate(lowestDate);
			}
		}else{
			if(simData.getTypeOfTransaction().equals(TransactionType.LONG_TFEX)){
				output.setGainLossAmount((highestDuringWeek - simData.getStartIndex()) * SimulationConstant.TFEX_PRICE_PER_POINT);
				output.setGainLossDate(highestDate);
			}else if(simData.getTypeOfTransaction().equals(TransactionType.SHORT_TEFX)){
				output.setGainLossAmount((simData.getStartIndex() - lowestDuringWeek) * SimulationConstant.TFEX_PRICE_PER_POINT);
				output.setGainLossDate(lowestDate);
			}else if(simData.getTypeOfTransaction().equals(TransactionType.BUY_SET50)){
				output.setGainLossAmount((simData.getStartIndex() - lowestDuringWeek)/simData.getStartIndex() * runningBalance);
				output.setGainLossDate(highestDate);
			}
		}
		
		return output;

	}

	public static double getClosingIndexOfWeek(
			Connection conn,
			MarketData thisWeek,
			String currencyPair,
			String marketName,
			String tradingType) throws SQLException {
		
		long cacheKey = 950000000000000l + thisWeek.getDate().getTime();
		
		Double closeIndex = (Double) GlobalCache.getCache(cacheKey);
		
		if(closeIndex != null){
			return closeIndex;
		}
		
		PreparedStatement ps = conn.prepareStatement(
				"SELECT CLOSE" +
				"  FROM PRICE_HIST " +
				" WHERE TRADING_DATE = ( SELECT MAX(TRADING_DATE)" +
				"						   FROM PRICE_HIST" +
				"						  WHERE CURRENCY_PAIR = ?" +
				"						    AND MARKET_NAME = ?" +
				"							AND TRADING_DATE < ?" +
				"							AND TRADING_TYPE = ?" +
				"                      )" +
				"   AND CURRENCY_PAIR = ?" +
				"   AND TRADING_TYPE = ?" +
				"   AND MARKET_NAME = ?");
		
		ps.setString(1, currencyPair);
		ps.setString(2, marketName);
		ps.setDate(3, thisWeek.getNextDate());
		ps.setString(4, tradingType);
		ps.setString(5, currencyPair);
		ps.setString(6, tradingType);
		ps.setString(7, marketName);
		
		
		ResultSet rs = ps.executeQuery();
		
		if(rs.next()){
			closeIndex = rs.getDouble("CLOSE");
		}
		
		rs.close();
		ps.close();
		
		GlobalCache.addCache(cacheKey, closeIndex);
		return closeIndex;
	}

	public static boolean hasToStopLoss(
			SimulationData simulationItem, 
			double lossPerLot, 
			double runningBalance,
			double lossStopThresholdPerContract, 
			double maxLossOnBalanceForTFEX) {
		if(simulationItem.getCutLossAmount_asDouble() == 0
							&& ((!simulationItem.getTypeOfTransaction().equals(TransactionType.BUY_SET50) && lossPerLot < lossStopThresholdPerContract)
									|| simulationItem.getTypeOfTransaction().equals(TransactionType.BUY_SET50) && lossPerLot < (maxLossOnBalanceForTFEX * runningBalance))){
			return true;
		}else{
			return false;
		}
	}

	public static boolean hasCutSMAV(SimulationData simulationItem, MarketData thisWeek, MarketData thisWeekTFEX) {
		
		if(simulationItem.getTypeOfTransaction().equals(TransactionType.LONG_TFEX)){
			if(thisWeekTFEX.getHighIndex() < thisWeekTFEX.getCloseIndexSMAV()){
				return true;
			}
		}else if(simulationItem.getTypeOfTransaction().equals(TransactionType.SHORT_TEFX)){
			if(thisWeekTFEX.getLowIndex() > thisWeekTFEX.getCloseIndexSMAV()){
				return true;
			}
		}else if(simulationItem.getTypeOfTransaction().equals(TransactionType.BUY_SET50)){
			if(thisWeek.getHighIndex() < thisWeek.getCloseIndexSMAV()){
				return true;
			}
		}
		return false;
	}

	public static double getProfitOrLossPerLotAtEndOfWeek(
			SimulationData simData, 
			MarketData thisWeek, 
			double runningBalance, 
			Connection conn, 
			String currencyPair, 
			String marketName, 
			String tradingType) throws SQLException{
		
		double output = 0;
		double closeIndex = getClosingIndexOfWeek(conn, thisWeek, currencyPair, marketName, tradingType);

		if(simData.getTypeOfTransaction().equals(TransactionType.LONG_TFEX)){
			output = (closeIndex - simData.getStartIndex())  * SimulationConstant.TFEX_PRICE_PER_POINT;
		}else if(simData.getTypeOfTransaction().equals(TransactionType.SHORT_TEFX)){
			output = (simData.getStartIndex() - closeIndex) * SimulationConstant.TFEX_PRICE_PER_POINT;
		}else if(simData.getTypeOfTransaction().equals(TransactionType.BUY_SET50)){
			output = (closeIndex - simData.getStartIndex()) / simData.getStartIndex() * runningBalance; 
		}
		
		return output;
	}
	
	public static String formatAmount(double amount){
		return doubleFormat.format(amount);
	}
	
	public static String formatInterest(double interest){
		return interestFormat.format(interest);
	}
	
	public static String formatInteger(long number){
		return integerFormat.format(number);
	}

	public static double getApproxEquivalentInterestPerYear(double initialCapital, double endedBalance, long days){
		
		int capPeriod = 180;
		double initInterest = 0.001f;
		double newEndedBalance = initialCapital;
		
		boolean reverseCalc = false;
		if(endedBalance < initialCapital){
			reverseCalc = true;
			double tmpFlt = initialCapital;
			initialCapital = endedBalance;
			endedBalance = tmpFlt;
		}
		
		long loopControl = 0;
		
		while(Math.abs((endedBalance - newEndedBalance)/endedBalance) > 0.003){
			
			int i=1;
			newEndedBalance = initialCapital;
			do{
				newEndedBalance = newEndedBalance * (1 + (initInterest / 2));
				i = i+capPeriod;
			}while(i <= days);
			
			initInterest += 0.0001f;
			loopControl++;
			if(loopControl > 4000000){
				throw new RuntimeException("Something wrong with interest calculation!!!");
			}
		}
		
		if(reverseCalc){
			return (-1 * initInterest);
		}else{
			return initInterest;
		}
	}
	
}
